package mysql;

import com.google.gson.stream.JsonWriter;

import java.io.IOException;
import java.io.StringWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Random;

public class MySqlTest {

    public static final String createSql = "CREATE TABLE IF NOT EXISTS test(k VARCHAR(32) NOT NULL PRIMARY KEY, v JSON NOT NULL) ENGINE=INNODB DEFAULT CHARSET=UTF8";
    public static final String replaceSql = "REPLACE INTO test VALUES(?, ?)";
    public static final String deleteSql = "DELETE FROM test WHERE k = ?";
    public static final String selectSql = "SELECT v FROM test WHERE k = ?";

    public static void main(String[] args) throws IOException {
        MySqlMgr.GetInstance().ExecUpdate(createSql, null);
        int count = 10000;
        testBatch(count);
        //testReplace(count);
        //testSelect(count);
        MySqlMgr.GetInstance().Exit();
    }

    public static void testBatch(int count) throws IOException {

        StringWriter sw = new StringWriter();
        JsonWriter _out_ = new JsonWriter(sw);
        _out_.beginObject();
        for (int i = 1; i <= 10; i++) {
            _out_.name(String.valueOf(i));
            _out_.value(i);
        }
        _out_.endObject();
        String value = sw.getBuffer().toString();
        _out_.close();

        long begin = System.currentTimeMillis();
        try {
            Connection conn = MySqlMgr.GetInstance().GetWriteConn();
            PreparedStatement pst = conn.prepareStatement(replaceSql);
            for (int i = 1; i <= count; i++) {
                pst.setString(1, String.valueOf(i));
                pst.setString(2, value);
                pst.addBatch();
                if (i % 10000 == 0) {
                    pst.executeBatch();
                    pst.clearBatch();
                    conn.commit();
                }
            }
            pst.executeBatch();
            pst.close();
            conn.commit();
            conn.close();
            System.out.println("testBatch " + count + " total time is " + (System.currentTimeMillis() - begin) + " ms");
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public static void testReplace(int count) throws IOException {
        long begin = System.currentTimeMillis();
        try {
            Connection conn = MySqlMgr.GetInstance().GetWriteConn();
            for (int i = 1; i <= count; i++) {
                PreparedStatement pst = conn.prepareStatement(replaceSql);
                pst.setString(1, String.valueOf(i));
                StringWriter sw = new StringWriter();
                JsonWriter _out_ = new JsonWriter(sw);
                _out_.beginObject();
                _out_.name(String.valueOf(i));
                _out_.value(i);
                _out_.endObject();
                pst.setString(2, sw.getBuffer().toString());
                _out_.close();
                pst.executeUpdate();
                pst.close();
            }
            conn.commit();
            conn.close();
            System.out.println("testReplace " + count + " total time is " + (System.currentTimeMillis() - begin) + " ms");
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public static void testSelect(int count) {
        long begin = System.currentTimeMillis();
        try {
            Connection conn = MySqlMgr.GetInstance().GetReadConn();
            Random random = new Random(System.currentTimeMillis());
            for (int i = 1; i <= count; i++) {
                PreparedStatement pst = conn.prepareStatement(selectSql);
                int key = random.nextInt(1000000);
                pst.setString(1, String.valueOf(key));
                ResultSet ret = pst.executeQuery();
                if (ret.next()) {
                }
                pst.close();
            }
            conn.close();
            System.out.println("testSelect " + count + " total time is " + (System.currentTimeMillis() - begin) + " ms");
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}
